package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.I_AD_OrgInfo;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MOrg;
import org.compiere.model.MOrgInfo;
import org.compiere.model.X_AD_OrgInfo;
import org.compiere.model.X_MM_Letter_Of_Attorney;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class MM_Form_D2 extends SvrProcess {
	//MM_Form_D2_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/** Period Parameter */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	/** Org Parameter */
	private int p_AD_Org_ID = 0;
	/** */
	String file;
	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DateAcct")) {
				p_DateAcct_From = (Timestamp) para[i].getParameter();
				p_DateAcct_To = (Timestamp) para[i].getParameter_To();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "MM_Form_D2_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		//Cell C = null;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql ="";
		
		//Report Header
		p_AD_Org_ID = Env.getAD_Org_ID(m_ctx);
		MOrg org = new MOrg(m_ctx, p_AD_Org_ID, get_TrxName());
		sheet.getRow(7).getCell(21).setCellValue(org.getName());
		sql = "SELECT COALESCE(fi_bin_iin,'0') FROM AD_OrgInfo WHERE IsActive = 'Y' AND AD_Client_ID = " +Env.getAD_Client_ID(m_ctx)+
				" AND AD_Org_ID = "+Env.getAD_Org_ID(m_ctx);
		try{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next())
				sheet.getRow(7).getCell(58).setCellValue(rs.getString(1));
		}catch(Exception e){
			log.log(Level.SEVERE, sql, e);
		}finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
//		sql = "SELECT erps_periodstring("+DB.TO_DATE(p_DateAcct_From,false)+","+DB.TO_DATE(p_DateAcct_To,false)+")";
//		String p_Period="";
//		try{
//			pstmt = DB.prepareStatement(sql, get_TrxName());
//			rs = pstmt.executeQuery();
//			while(rs.next()){
//				p_Period = rs.getString(1);
//				sheet.getRow(10).getCell(32).setCellValue(p_Period);
//			}
//		}catch(Exception e){
//			log.log(Level.SEVERE, sql, e);
//		}finally{
//			DB.close(rs, pstmt);
//			rs = null; pstmt = null;
//		}
		sheet.getRow(10).getCell(32).setCellValue("за "+new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_From)+" - "+new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_To)+" года");
		//
		sql = "SELECT MM_Letter_Of_Attorney_ID," +
				"\n(SELECT HRM_ProfessionalExperience.HRM_Position  " +
					"FROM HRM_ProfessionalExperience HRM_ProfessionalExperience  " +
					"WHERE HRM_ProfessionalExperience.C_BPartner_ID = MM_Letter_of_attorney.C_BPartner_ID) " +
				"\nFROM MM_Letter_Of_Attorney " +
				"\nWHERE DateIssue BETWEEN "+ DB.TO_DATE(p_DateAcct_From,false) 
				+" AND "+DB.TO_DATE(p_DateAcct_To,false)+
				" \nORDER BY DateIssue";
		try{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			int rowNo = 14;
			//int cellNo = 0;
			X_MM_Letter_Of_Attorney letter_Of_Attorney = null;
			String hrm_position = "";
			while(rs.next()){
				letter_Of_Attorney = new X_MM_Letter_Of_Attorney(m_ctx, rs.getInt(1), get_TrxName());
				hrm_position = rs.getString(2);
				sheet.shiftRows(rowNo, rowNo+1, 1);
				R = sheet.createRow(rowNo);
				for(int i = 0; i< 65; i++){
					R.createCell(i);
					R.getCell(i).setCellStyle(sheet.getRow(12).getCell(64).getCellStyle());
				}
				//col1
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 4));
				R.getCell(0).setCellValue(letter_Of_Attorney.getDocumentNo());
				R.getCell(0).setCellStyle(sheet.getRow(12).getCell(0).getCellStyle());
				//col2
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 5, 9));
				R.getCell(5).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(letter_Of_Attorney.getdateissue()));
				//col3
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 10, 14));
				R.getCell(10).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(letter_Of_Attorney.getValidTo()));
				//col4
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 15, 32));
				R.getCell(15).setCellValue(hrm_position+" "+letter_Of_Attorney.getC_BPartner().getName());
				//col5
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 33, 49));
				R.getCell(33).setCellValue(letter_Of_Attorney.getVendor().getName());
				//col6
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 50, 54));
				//col7
				sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 55, 64));
				rowNo++;
			}
		}catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));	
		return Msg.translate(m_ctx, "Success");
	}

}
